package com.info.zhiduoduo.repository.mapper.maker;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.maker.*;
import com.info.zhiduoduo.repository.entity.crowdsource.CrowdsourceAccount;
import com.info.zhiduoduo.repository.entity.maker.MakerStudio;

import com.info.zhiduoduo.repository.entity.memorandum.PlatformMemorandum;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 创客个人工作室资料 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2018-12-26
 */
public interface MakerStudioMapper extends BaseMapper<MakerStudio> {

	@Select("select count(userid) from zdd_maker_studio where userid = #{userid} and state = 2")
	boolean isStudioOpen(@Param("userid") int userid);

	@Select(" select userid from zdd_maker_studio where open_tax_acct = 1 ")
	List<String> getAllOpenTaxAcctIds();

	@Select(" select us.*,ua.realname,ua.identity_type,ua.idno,ua.idfrontimg,ua.idbackimg,ua.educationimg,ua.identificationimg," +
			" ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason,ua.core_module,ua.opening_location," +
			" ua.is_registered_studio,ua.phone_of_emergency_contact,ua.name_of_emergency_contact,ua.temporary_login," +
			" ub.payername,ub.cardtype,ub.bankname,ub.acctno,ub.telephone,ub.open_acct_bank_name," +
			" ub2.payername as payername2,ub2.cardtype as cardtype2,ub2.bankname bankname2,ub2.bankno bankno2," +
			" ub2.telephone as telephone2,ub2.acctno as acctno2,ub2.idno as acctIdno2," +
			" ub2.open_acct_bank_name as openAcctBankName2,ub2.province as bankProvince2,ub2.city as bankCity2," +
			" u.usertype,u.bindcompid,c.compname as bindcompname,u.compremark,u.has_company,u.has_contract,u.work_company,ua.customer_manager," +
			" u.maker_type,u.payid,u.issue_date,u.expire_date,ua.education,ua.political,ua.email,ua.residence,ua.industry,ua.occupation," +
			" u.invite_code,u.notice_flag,pa.realname as inviter,u.platform_hosting,u.custodian_id," +
			" (select uar.remark from zdd_maker_auth_record uar " +
			" where uar.acctid = ua.acctid and uar.state = 0 order by uar.id limit 1) as authRemark, " +
			" ua.maker_remarks,ub.alipay_account,ub.payment_account_status,ub2.payment_account_status AS paymentAccountStatusTwo,ua.whether_an_external_studio " +
			" from zdd_maker_studio us " +
			" join zdd_maker u on u.userid = us.userid " +
			" join zdd_maker_account ua on ua.userid = us.userid " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" left join zdd_maker_bankcard ub on ub.userid = us.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_maker_bankcard ub2 on ub2.userid = us.userid and ub2.state = 1 and ub2.accttype = 2 " +
			" left join zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
			" left join zdd_taxer_payment tp on tp.userid = u.userid and tp.type = 1 and tp.deleted = 0 " +
			" where us.userid = #{userid}")
	StudioDetailDto getStudioDetail(@Param("userid") int userid);

	@Select(" select us.*,ua.realname,ua.identity_type,ua.idno,ua.maker_label,tc.tradecategory,tp.state as paidState,ua.expert_label_state," +
			" ub.cardtype,ub.bankname,ub.acctno,ub.telephone, " +
			" ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason,ua.core_module,ua.opening_location, " +
			" ua.temporary_login,u.phone,u.usertype,u.bindcompid,c.compname as bindcompname,u.compremark,u.has_company,u.has_contract,u.work_company," +
			" u.maker_type,u.payid,u.issue_date,u.expire_date," +
			" IF(IFNULL(us.studiotaxphoto,'')='',0,1) AS hasTaxPhoto,u.invite_code,u.notice_flag,pa.realname as inviter,count(mem.content) as needCount,  " +
			" CASE WHEN ali.userid is null THEN 0 ELSE 1 END as isOpenAliPay," +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2," +
			" CASE WHEN pa.studio_id > 0 THEN 1 ELSE 0 END as partnerStudio," +
			" c.friendly_state as friendlyCompany," +
			" IF(u.openid != '' or u.taxer_openid != '' or u.crowdsource_openid != '', 1, 0) as voluntary," +
			" CASE " +
			"   WHEN u.state = 4 THEN 3 " +
			"   WHEN us.open_tax_acct = 1 THEN 5 " +
			"   WHEN us.state = 2 THEN 4 " +
			"   WHEN ua.realname_auth = 4 THEN 2 " +
			"   ELSE 1 " +
			" END as unifiedState,u.createtime AS makerCreateTime, " +
			" ${str} "+
			" from zdd_maker_studio us " +
			" join zdd_maker u on u.userid = us.userid " +
			" join zdd_maker_account ua on ua.userid = us.userid " +
			" left join zdd_system_tradecategory tc on tc.tradecategoryid = us.tradecategoryid " +
			" left join zdd_maker_bankcard ub on ub.userid = us.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" left join zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
			" left join zdd_taxer_payment tp on tp.userid = u.userid and tp.type = 1 and tp.deleted = 0 " +
			" left join zdd_platform_memorandum mem on mem.customer_id=us.userid"+
			" left join zdd_maker_alipay ali on ua.userid=ali.userid and ali.deleted=0"+
			" left join zdd_maker_bankcard ub2 on ub2.userid = us.userid and ub2.state = 1 and ub2.accttype = 2 "+
			" where us.state = #{state} and u.state != 4 " +
			" and us.tax_acct_state != 3 " +
			" AND IF(#{makerType} = 1, u.maker_type in (1,3), u.maker_type = #{makerType})" +
//			" and u.maker_type = #{makerType} " +
			" and us.createtime BETWEEN #{startDate} AND #{endDate} " +
			" and (ua.realname like '%${name}%' or us.username like '%${name}%') " +
			" AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa.promo_code,pa.realname) like '%${inviter}%') " +
			" AND IF(ISNULL(#{bindcompname}) OR #{bindcompname}='',1=1,c.compname like '%${bindcompname}%') " +
			" AND IF(ISNULL(#{tradecategoryid}) OR #{tradecategoryid}=0,1=1,us.tradecategoryid=#{tradecategoryid}) " +
			" AND IF( ISNULL(#{openingLocation}) , 1=1 , ua.opening_location = #{openingLocation}  )   " +
			" AND IF(ISNULL(#{temporaryLogin}), 1=1, ua.temporary_login = #{temporaryLogin})" +
			" AND IF(ISNULL(#{quotaFlag}), 1=1, us.quota_flag = #{quotaFlag})" +
			" and if(isnull(#{expertLabelState}),1=1,ua.expert_label_state=#{expertLabelState}) " +
			" and if(isnull(#{makerLabel}), 1=1, ua.maker_label like '%${makerLabel}%')" +
			" AND IF(ISNULL(#{whetherAnExternalStudio}) OR #{whetherAnExternalStudio}='',1=1,ua.whether_an_external_studio = #{whetherAnExternalStudio}) " +
			" ${dockingCity} " +
			" group by us.userid having IF(#{changedDefaultRate}=0,1=1,changedDefaultRate=1) " +
			"	and IF(isnull(#{isOpenAliPay}),1=1,isOpenAliPay=#{isOpenAliPay})" +
			"	and IF(isnull(#{isOpenAccNo2}),1=1,isOpenAccNo2=#{isOpenAccNo2})" +
			" ORDER BY us.userid DESC ")
	List<StudioBriefDto> getStudioList(@Param("makerType") int makerType, @Param("state") int state, @Param("name") String name, @Param(
			"inviter") String inviter, @Param("bindcompname") String bindcompname,
	                                   @Param("tradecategoryid") Integer tradecategoryid, @Param("startDate") Date startDate, @Param("endDate") Date endDate, @Param("openingLocation") Integer openingLocation,
	                                   @Param("changedDefaultRate") Integer changedDefaultRate, @Param("str") String str,
	                                   @Param("dockingCity") String dockingCity, @Param("temporaryLogin") Integer temporaryLogin,
									   @Param("expertLabelState")Integer expertLabelState,@Param("quotaFlag") Integer quotaFlag,
									   @Param("isOpenAliPay")Integer isOpenAliPay,@Param("makerLabel") String makerLabel,
									   @Param("isOpenAccNo2")Integer isOpenAccNo2,
									   @Param("whetherAnExternalStudio") Integer whetherAnExternalStudio);

	@Select(" select us.*,ua.realname,ua.identity_type,ua.idno,ua.maker_label,tc.tradecategory,tp.state as paidState,ua.expert_label_state," +
			" ub.cardtype,ub.bankname,ub.acctno,ub.telephone, " +
			" ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason,ua.core_module,ua.opening_location, " +
			" ua.temporary_login,u.phone,u.usertype,u.bindcompid,c.compname as bindcompname,u.compremark,u.has_company,u.has_contract,u.work_company," +
			" u.maker_type,u.payid,u.issue_date,u.expire_date," +
			" IF(IFNULL(us.studiotaxphoto,'')='',0,1) AS hasTaxPhoto,u.invite_code,u.notice_flag,pa.realname as inviter,count(mem.content) as needCount, " +
			" CASE WHEN ali.userid is null THEN 0 ELSE 1 END as isOpenAliPay," +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2," +
			" CASE WHEN pa.studio_id > 0 THEN 1 ELSE 0 END as partnerStudio," +
			" c.friendly_state as friendlyCompany," +
			" IF(u.openid != '' or u.taxer_openid != '' or u.crowdsource_openid != '', 1, 0) as voluntary," +
			" CASE " +
			"   WHEN u.state = 4 THEN 3 " +
			"   WHEN us.open_tax_acct = 1 THEN 5 " +
			"   WHEN us.state = 2 THEN 4 " +
			"   WHEN ua.realname_auth = 4 THEN 2 " +
			"   ELSE 1 " +
			" END as unifiedState,u.createtime AS makerCreateTime, " +
			" ${str} "+
			" from zdd_maker_studio us " +
			" join zdd_maker u on u.userid = us.userid " +
			" join zdd_maker_account ua on ua.userid = us.userid " +
			" left join zdd_system_tradecategory tc on tc.tradecategoryid = us.tradecategoryid " +
			" left join zdd_maker_bankcard ub on ub.userid = us.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" left join zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
			" left join zdd_taxer_payment tp on tp.userid = u.userid and tp.type = 1 and tp.deleted = 0 " +
			" left join zdd_platform_memorandum mem on mem.customer_id=us.userid"+
			" left join zdd_maker_alipay ali on ua.userid=ali.userid and ali.deleted=0"+
			" left join zdd_maker_bankcard ub2 on ub2.userid = us.userid and ub2.state = 1 and ub2.accttype = 2 "+
			" where if(#{taxAcctState}=0, 1=1, us.tax_acct_state = #{taxAcctState}) " +
			" AND IF(#{makerType} = 1, u.maker_type in (1,3), u.maker_type = #{makerType})" +
//			" and u.maker_type = #{makerType} " +
			" and u.state != 4 " +
			" and us.createtime BETWEEN #{startDate} AND #{endDate} " +
			" and (CONCAT(ua.realname,us.username) like '%${name}%') " +
			" AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa.promo_code,pa.realname) like '%${inviter}%') " +
			" AND IF(ISNULL(#{bindcompname}) OR #{bindcompname}='',1=1,c.compname like '%${bindcompname}%') " +
			" AND IF(ISNULL(#{tradecategoryid}) OR #{tradecategoryid}=0,1=1,us.tradecategoryid=#{tradecategoryid}) " +
			" AND IF(ISNULL(#{openingLocation}) , 1=1 , ua.opening_location = #{openingLocation}) " +
			" AND IF(ISNULL(#{temporaryLogin}), 1=1, ua.temporary_login = #{temporaryLogin})" +
			" AND IF(ISNULL(#{quotaFlag}), 1=1, us.quota_flag = #{quotaFlag})" +
			" and if(isnull(#{expertLabelState}),1=1,ua.expert_label_state=#{expertLabelState}) " +
			" and if(isnull(#{makerLabel}), 1=1, ua.maker_label like '%${makerLabel}%')" +
			" AND IF(ISNULL(#{whetherAnExternalStudio}) OR #{whetherAnExternalStudio}='',1=1,ua.whether_an_external_studio = #{whetherAnExternalStudio}) " +
			" ${dockingCity} " +
			" group by us.userid having IF(#{changedDefaultRate}=0,1=1,changedDefaultRate = 1) " +
			" 	and IF(isnull(#{isOpenAliPay}),1=1,isOpenAliPay=#{isOpenAliPay}) " +
			"	and IF(isnull(#{isOpenAccNo2}),1=1,isOpenAccNo2=#{isOpenAccNo2})" +
			" ORDER BY us.userid DESC ")
	List<StudioBriefDto> getOpenTaxAcctStudioList(@Param("taxAcctState") int taxAcctState, @Param("makerType") int makerType,
	                                              @Param("name") String name, @Param("inviter") String inviter,
	                                              @Param("bindcompname") String bindcompname,
	                                              @Param("tradecategoryid") Integer tradecategoryid,
	                                              @Param("startDate") Date startDate, @Param("endDate") Date endDate,
	                                              @Param("openingLocation") Integer openingLocation,
	                                              @Param("changedDefaultRate") Integer changedDefaultRate, @Param("str") String str,
	                                              @Param("dockingCity") String dockingCity,
	                                              @Param("temporaryLogin") Integer temporaryLogin,
												  @Param("expertLabelState")Integer expertLabelState,@Param("quotaFlag") Integer quotaFlag,
												  @Param("isOpenAliPay")Integer isOpenAliPay, @Param("makerLabel")String makerLabel,
												  @Param("isOpenAccNo2")Integer isOpenAccNo2,
												  @Param("whetherAnExternalStudio") Integer whetherAnExternalStudio);

	@Select(" select us.userid,us.username,us.studionickname,ua.realname,ua.identity_type,ua.idno,us.tradecategoryid,tc.tradecategory," +
			" ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason,us.brokerage_compute_state,ua.temporary_login," +
			" us.studiophone,us.province,us.addr,us.specialinvoice,us.invoicescope,us.state,us.agent,us.agentphone,us.crowdsource_convert_zdd_maker," +
			" us.taxer,us.taxer_paid,us.taxertime,us.tax_acct_state,us.open_tax_acct,us.createtime,us.updatetime,tp.state as paidState," +
			" us.entrust_tax_video_state,us.entrust_tax_video_remark," +
			" ub.cardtype,ub.bankname,ub.acctno,ub.telephone," +
			" u.phone,u.usertype,u.bindcompid,c.compname as bindcompname,u.compremark,u.has_company,u.has_contract,u.work_company," +
			" u.maker_type,u.payid,u.issue_date,u.expire_date," +
			" IF(IFNULL(us.studiotaxphoto,'')='',0,1) AS hasTaxPhoto,u.invite_code,u.notice_flag,pa.realname as inviter," +
			" u.cancel_voucher " +
			" from zdd_maker_studio us " +
			" join zdd_maker u on u.userid = us.userid " +
			" join zdd_maker_account ua on ua.userid = us.userid " +
			" left join zdd_system_tradecategory tc on tc.tradecategoryid = us.tradecategoryid " +
			" left join zdd_maker_bankcard ub on ub.userid = us.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" left join zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
			" left join zdd_taxer_payment tp on tp.userid = u.userid and tp.type = 1 and tp.deleted = 0 " +
			" where if(#{taxAcctState}=0, 1=1, us.tax_acct_state = #{taxAcctState}) " +
			" AND IF(#{makerType} = 1, u.maker_type in (1,3), u.maker_type = #{makerType})" +
//			" and u.maker_type = #{makerType}" +
			" and u.state = 4 " +
			" and us.createtime BETWEEN #{startDate} AND #{endDate} " +
			" and (CONCAT(ua.realname,us.username) like '%${name}%') " +
			" AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa.promo_code,pa.realname) like '%${inviter}%') " +
			" AND IF(ISNULL(#{bindcompname}) OR #{bindcompname}='',1=1,c.compname like '%${bindcompname}%') " +
			" AND IF(ISNULL(#{tradecategoryid}) OR #{tradecategoryid}=0,1=1,us.tradecategoryid=#{tradecategoryid}) " +
			" ${dockingCity} " +
			" ORDER BY us.userid DESC ")
	List<StudioBriefDto> getRescissionList(@Param("taxAcctState") int taxAcctState, @Param("makerType") int makerType, @Param("name") String name,
												  @Param("inviter") String inviter, @Param("bindcompname") String bindcompname,
												  @Param("tradecategoryid") Integer tradecategoryid, @Param("startDate") Date startDate, @Param("endDate") Date endDate,
										   @Param("dockingCity") String dockingCity);

	@Select(" select us.*,ua.realname,ua.identity_type,ua.idno,ua.idfrontimg,ua.idbackimg,ua.educationimg,ua.identificationimg," +
			" ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason,ua.core_module,ua.temporary_login," +
			" ub.payername,ub.cardtype,ub.bankname,ub.acctno,ub.telephone,ub.open_acct_bank_name," +
			" ub2.payername as payername2,ub2.cardtype as cardtype2,ub2.bankname bankname2,ub2.bankno bankno2," +
			" ub2.telephone as telephone2,ub2.acctno as acctno2,ub2.idno as acctIdno2," +
			" ub2.open_acct_bank_name as openAcctBankName2,ub2.province as bankProvince2,ub2.city as bankCity2," +
			" u.usertype,u.bindcompid,c.compname as bindcompname,u.compremark,u.has_company,u.has_contract,u.work_company,ua.customer_manager," +
			" u.maker_type,u.payid,u.issue_date,u.expire_date,ua.education,ua.political,ua.email,ua.residence,ua.industry,ua.occupation," +
			" u.invite_code,u.notice_flag,pa.realname as inviter," +
			" (select uar.remark from zdd_maker_auth_record uar " +
			" where uar.acctid = ua.acctid and uar.state = 0 order by uar.id limit 1) as authRemark " +
			" from zdd_maker_studio us " +
			" join zdd_maker u on u.userid = us.userid " +
			" join zdd_maker_account ua on ua.userid = us.userid " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" left join zdd_maker_bankcard ub on ub.userid = us.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_maker_bankcard ub2 on ub2.userid = us.userid and ub2.state = 1 and ub2.accttype = 2 " +
			" left join zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
			" left join zdd_taxer_payment tp on tp.userid = u.userid and tp.type = 1 and tp.deleted = 0 " +
			" where ua.idno = #{idno} " +
			" order by us.userid desc " +
			" limit 1 ")
	StudioDetailDto getByIdno(@Param("idno") String idno);

	/**
	 * 查询项目绑定的创客的工作室信息
	 * @param projectid
	 * @return
	 */
	@Select(" SELECT ms.* " +
			" FROM zdd_crowdsource_project_item cpi " +
			" LEFT JOIN zdd_maker_studio ms ON ms.userid = cpi.userid and cpi.user_type = 2 " +
			" WHERE cpi.projectid = #{projectid} AND cpi.deleted = 0 ")
	List<MakerStudio> queryMakerStudioInfo(Integer projectid);


	@Select(" select u.userid,ua.realname,ua.realname_auth,ua.identity_type,ua.idno,ua.createtime,ua.updatetime," +
			" ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason,ua.core_module,ua.opening_location, " +
			" ua.temporary_login,ua.gong_qing_data_submint,ua.gong_qing_detection_failure_count,ua.gong_qing_detection_failure_reason,ua.gong_qing_detection_failure, " +
			" ua.government_examine_state,ua.government_rejection_reasons, " +
			" us.username,us.studionickname,us.studiophone,us.province,us.addr,us.tradecategoryid,us.specialinvoice,us.invoicescope," +
			" us.state,us.agent,us.agentphone,us.tax_acct_state,us.open_tax_acct,us.taxer,us.taxer_paid,us.taxertime,us.quota_flag," +
			" us.twelve_month_quota_flag,us.entrust_tax_video_state,us.entrust_tax_video_remark," +
			" tc.tradecategory,tp.state as paidState,ub.cardtype,ub.bankname,ub.acctno,ub.telephone,us.crowdsource_convert_zdd_maker," +
			" u.phone,u.usertype,u.bindcompid,c.compname as bindcompname,u.compremark,u.has_company,u.has_contract,u.work_company," +
			" u.maker_type,u.payid,u.issue_date,u.expire_date," +
			" IF(IFNULL(us.studiotaxphoto,'')='',0,1) AS hasTaxPhoto,u.invite_code,u.notice_flag,pa.realname as inviter," +
			" IF(ua.realname_auth = 3,uar.reason,'') as reason " +
			" from zdd_maker u " +
			" join zdd_maker_account ua on ua.userid = u.userid " +
			" LEFT JOIN (SELECT acctid,remark,reason,state from zdd_maker_auth_record GROUP BY acctid ORDER BY id desc)as uar on uar.acctid = ua.acctid" +
			" left join zdd_maker_studio us on us.userid = u.userid " +
			" left join zdd_system_tradecategory tc on tc.tradecategoryid = us.tradecategoryid " +
			" left join zdd_maker_bankcard ub on ub.userid = u.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" left join zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
			" left join zdd_taxer_payment tp on tp.userid = u.userid and tp.type = 1 and tp.deleted = 0 " +
			" where CASE #{queryState} " +
			" WHEN 0 THEN 1=1 " +
			" WHEN 1 THEN ua.realname_auth = 4 " +
			" WHEN 2 THEN ua.realname_auth = 5 " +
			" ELSE 1=0 " +
			" END " +
			" and u.state != 4 " +
			" and ua.temporarily_not_handled = 0 " +
			" and IF(#{taxAcctState}=0, 1=1, us.tax_acct_state = #{taxAcctState}) " +
			" AND IF(#{bindcompid}=0, 1=1, u.bindcompid = #{bindcompid}) " +
			" and CONCAT(IFNULL(ua.realname, ''),IFNULL(us.username, '')) like '%${name}%' " +
			" and ua.createtime BETWEEN #{startDate} AND #{endDate} " +
			" ORDER BY u.userid DESC ")
	List<StudioBriefDto> getCompanyStudioList(@Param("name") String name, @Param("queryState") int queryState, @Param("taxAcctState") int taxAcctState,
	                                          @Param("bindcompid") int bindcompid, @Param("startDate") Date startDate,
	                                          @Param("endDate") Date endDate);



	/**
	 *查找存在两个工作室以上的创客用户
	 *
	 * @Author wang
	 * @date 2021/4/14
	 **/
   @Select(" select a.realname,s.username,m.state,s.open_finishtime,m.logouttime,a.idno,com.compname," +
		   " CASE " +
		   "   WHEN s.taxer = 1  AND m.maker_type != 2 THEN 4 " +
		   "   ELSE m.maker_type " +
		   " END as makerType," +
		   " CASE " +
		   "   WHEN m.state = 4 THEN 3 " +
		   "   WHEN s.open_tax_acct = 1 THEN 5 " +
		   "   WHEN s.state = 2 THEN 4 " +
		   "   WHEN a.realname_auth = 4 THEN 2 " +
		   "   ELSE 1 " +
		   " END as unifiedState," +
		   " a.temporary_login as temporaryLogin," +
		   " IF(m.openid != '' or m.taxer_openid != '' or m.crowdsource_openid != '', 1, 0) as voluntary," +
		   " a.expert_label_state as expertLabelState," +
		   " IF(a.maker_label like '%1%', 1, 0) as friendlyMaker," +
		   " com.friendly_state as friendlyCompany," +
		   " CASE WHEN mal.userid > 0 THEN 1 ELSE 0 END as isOpenAlipay," +
		   " CASE WHEN pa.studio_id > 0 THEN 1 ELSE 0 END as partnerStudio," +
		   " a.opening_location as openingLocation," +
		   " CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2," +
		   " case when zca.acctid is null then 0 else 1 end as naturalPersonStatus "+
		   " from zdd_maker_studio s " +
		   " left join zdd_maker m on m.userid=s.userid " +
		   " left join zdd_maker_account a on s.userid=a.userid " +
		   " left join zdd_company com on  com.compid= m.bindcompid " +
		   " left join zdd_maker_alipay mal on mal.userid = m.userid and mal.deleted=0 " +
		   " left join zdd_partner_account pa on pa.studio_id = m.userid " +
		   " left join zdd_crowdsource_account zca on zca.idno = a.idno"+
		   " left join zdd_maker_bankcard ub2 on ub2.userid = s.userid and ub2.state = 1 and ub2.accttype = 2 "+
		   " WHERE left(a.idno,18) in(select left(idno,18) " +
		   " from zdd_maker_account acc " +
		   " left join zdd_maker_studio stu " +
		   " on acc.userid=stu.userid " +
		   " where stu.username is not null " +
		   " group by left(idno,18) HAVING SUM(1) > 1) " +
		   " and if(isnull(#{name}),1=1,(s.username like concat('%',#{name},'%') or a.realname like concat('%',#{name},'%'))) " +
		   " order by a.idno desc")
	  List<MakerStudioDto> findMakerStudio(@Param("name") String name);

	/**
	 *查看 的交易额度
	 *
	 *
	 **/
	@Select("select stu.open_finishtime ,acc.realname,stu.username,com.compname,\n" +
			"SUM(if(DATE_FORMAT(pro.projectstart,'%Y-%m')=DATE_FORMAT(now(),'%Y-%m'),IFNULL(pro.projectdealamount,0),0)) monthBusiness,\n" +
			"SUM(if(QUARTER(pro.projectstart)=QUARTER(NOW()) and year(pro.projectstart)=year(now()),IFNULL(pro.projectdealamount,0),0)) quarterBusiness,\n" +
			"SUM(if(DATE_FORMAT(pro.projectstart,'%Y')=DATE_FORMAT(now(),'%Y'),IFNULL(pro.projectdealamount,0),0)) yearBusiness\n" +
			"from  zdd_maker_studio stu\n" +
			"left join  zdd_maker_account acc  on acc.userid=stu.userid\n" +
			"left join zdd_maker mak on mak.userid=stu.userid\n" +
			"left join zdd_company com on com.compid=mak.bindcompid\n" +
			"left join zdd_company_project pro on pro.userid=stu.userid \n" +
			"WHERE mak.state != 4 and mak.maker_type=1 and ${str}\n" +
			"group by stu.userid\n" +
			"order by  monthBusiness desc\n")
	List<MakerBalanceDto> queryBalanceCompany(@Param("str") String str);

	/**
	 *查看智众包的交易额度
	 *
	 *
	 **/
	@Select("select stu.open_finishtime ,acc.realname,stu.username,com.compname,\n" +
			"SUM(if(DATE_FORMAT(item.createtime,'%Y-%m')=DATE_FORMAT(now(),'%Y-%m'),IFNULL(item.project_amount,0),0)) monthBusiness,\n" +
			"SUM(if(QUARTER(item.createtime)=QUARTER(NOW()) and year(item.createtime)=year(now()),IFNULL(item.project_amount,0),0)) quarterBusiness,\n" +
			"SUM(if(DATE_FORMAT(item.createtime,'%Y')=DATE_FORMAT(now(),'%Y'),IFNULL(item.project_amount,0),0)) yearBusiness\n" +
			"from zdd_maker_studio stu\n" +
			"left join zdd_crowdsource_project_item item on item.userid=stu.userid \n" +
			"left join zdd_maker_account acc on stu.userid=acc.userid\n" +
			"left join zdd_crowdsource_project pro on pro.projectid=item.projectid\n" +
			"left join zdd_maker mak on mak.userid=stu.userid\n" +
			"left join zdd_company com on com.compid=mak.bindcompid\n" +
			"where mak.maker_type=3  and stu.state=2 and mak.state != 4 and ${str}\n" +
			"group by stu.userid\n"+
			"order by  monthBusiness desc")
	List<MakerBalanceDto> queryBalanceCrowdsoucre(@Param("str") String str);

	/**
	 *查看智节税+存代理的交易额度
	 *
	 *
	 **/
	@Select("select stu.open_finishtime,acc.realname,stu.username,if(mak.maker_type=1,'纯代理',' ') makerType,com.compname, \n" +
			"SUM(if(DATE_FORMAT(pro.createtime,'%Y-%m')=DATE_FORMAT(now(),'%Y-%m'),IFNULL(inv.amount,0),0)) monthBusiness,\n" +
			"SUM(if(QUARTER(pro.createtime)=QUARTER(NOW()) and year(pro.createtime)=year(now()),IFNULL(inv.amount,0),0)) quarterBusiness,\n" +
			"SUM(if(DATE_FORMAT(pro.createtime,'%Y')=DATE_FORMAT(now(),'%Y'),IFNULL(inv.amount,0),0)) yearBusiness\n" +
			"from zdd_maker_studio stu\n" +
			"left join zdd_taxer_invoice_project pro on pro.userid=stu.userid \n" +
			"left join zdd_maker_account acc on stu.userid=acc.userid\n" +
			"left join zdd_taxer_invoice inv on inv.projectid=pro.projectid\n" +
			"left join zdd_maker mak on mak.userid=stu.userid\n" +
			"left join zdd_company com on com.compid=mak.bindcompid\n" +
			"where mak.state != 4 and mak.maker_type!=3 and stu.taxer=1 and ${str}\n" +
			"group by stu.userid\n"+
			"order by  monthBusiness desc")
	List<MakerBalanceDto> queryBalanceTaxerInvoice(@Param("str") String str);

	/**
	 * 查询所有已完成工作室开户，已开税户的工作室
	 *
	 * @param str
	 * @return
	 */
	@Select(" select s.userid,s.open_finishtime,s.username,s.taxer,a.realname,m.maker_type,com.compname," +
			" CASE " +
			"   WHEN m.state = 4 THEN 3 " +
			"   WHEN s.open_tax_acct = 1 THEN 5 " +
			"   WHEN s.state = 2 THEN 4 " +
			"   WHEN a.realname_auth = 4 THEN 2 " +
			"   ELSE 1 " +
			" END as unifiedState," +
			" a.temporary_login as temporaryLogin," +
			" IF(m.openid != '' or m.taxer_openid != '' or m.crowdsource_openid != '', 1, 0) as voluntary," +
			" a.expert_label_state as expertLabelState," +
			" IF(a.maker_label like '%1%', 1, 0) as friendlyMaker," +
			" com.friendly_state as friendlyCompany," +
			" CASE WHEN mal.userid > 0 THEN 1 ELSE 0 END as isOpenAlipay," +
			" CASE WHEN pa.studio_id > 0 THEN 1 ELSE 0 END as partnerStudio," +
			" a.opening_location as openingLocation," +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2," +
			" case when zca.acctid is null then 0 else 1 end as naturalPersonStatus "+
			" from zdd_maker_studio s" +
			" left join zdd_maker_account a on s.userid=a.userid" +
			" left join zdd_maker m on m.userid=s.userid" +
			" left join zdd_company com on com.compid=m.bindcompid" +
			" left join zdd_maker_alipay mal on mal.userid = m.userid and mal.deleted=0 " +
			" left join zdd_partner_account pa on pa.studio_id = m.userid " +
			" left join zdd_crowdsource_account zca on zca.idno = a.idno"+
			" left join zdd_maker_bankcard ub2 on ub2.userid = s.userid and ub2.state = 1 and ub2.accttype = 2 "+
			" left join zdd_city_config zcc on zcc.id = a.opening_location" +
			" where ${str}" +
			" and s.state = 2 and a.realname_auth !=1 and a.realname_auth !=2 and a.realname_auth !=3 ")
	List<MakerBalanceDto> queryAllMakerStudio(@Param("str") StringBuilder str);

	/**
	 *  查询创客对象的专家团详情
	 * @param id 工作室id
	 * @return
	 */

     @Select("select acc.userid,stu.intro,acc.expert_label_state,acc.expert_industry,acc.expert_industry_label,acc.expert_region_label, " +
			 " acc.expert_skill_expertise,acc.expert_label,acc.expert_remarks,acc.residence as makerResidence,com.compname,acc.createtime,acc.expert_industry_label, " +
			 " acc.realname,acc.realname_auth,stu.username  " +
			 " from zdd_maker_account acc " +
			 " left join zdd_maker_studio stu on stu.userid=acc.userid" +
			 " left join zdd_maker mak on stu.userid=mak.userid " +
			 " left join zdd_company com on mak.bindcompid=com.compid  " +
			 " where acc.userid=#{id} ")
	QueryExpertLabelDto queryExpertLabel(@Param("id") Integer id);

	@Select("select acc.acctid, acc.userid,if(isnull(stu.intro),acc.expert_intro,stu.intro) as intro,acc.expert_label_state,acc.expert_industry,acc.expert_industry_label,acc.expert_region_label,mak.maker_type, " +
			" acc.expert_skill_expertise,acc.expert_label,acc.expert_remarks,CONCAT(a.name,',',b.name) as makerResidence ,acc.createtime,acc.realname,stu.username,com.compname,acc.realname_auth " +
			" from zdd_maker_account acc " +
			" left join zdd_maker_studio stu on stu.userid=acc.userid " +
			" left join zdd_maker mak on acc.userid=mak.userid " +
			" left join zdd_company com on mak.bindcompid=com.compid  " +
			" left join zdd_address a on a.id= SUBSTRING_INDEX(acc.residence, ',',1) " +
			" left join zdd_address b on b.id= SUBSTRING_INDEX(acc.residence, ',',-1) " +
			" where acc.expert_label_state=1 AND mak.state != 4  " +
			" and  if(isnull(#{condition}) or #{condition}='', 1=1," +
			" acc.realname like '%${condition}%' or stu.username like '%${condition}%' " +
			" or com.compname like '%${condition}%' or acc.residence like '%${condition}%' or a.name like '%${condition}%' or b.name like '${condition}' )" +
			" order by acc.createtime desc ")
	List<QueryExpertLabelDto> queryExpertLabelTab(@Param("condition")String condition);

	@Select(" select us.*,ua.realname,ua.identity_type,ua.idno,ua.maker_label,tc.tradecategory,tp.state as paidState,ua.expert_label_state," +
			" ub.cardtype,ub.bankname,ub.acctno,ub.telephone, " +
			" ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason,ua.core_module,ua.opening_location, " +
			" ua.temporary_login,u.phone,u.usertype,u.bindcompid,c.compname as bindcompname,u.compremark,u.has_company,u.has_contract,u.work_company," +
			" u.maker_type,u.payid,u.issue_date,u.expire_date," +
			" IF(IFNULL(us.studiotaxphoto,'')='',0,1) AS hasTaxPhoto,u.invite_code,u.notice_flag,pa.realname as inviter,count(mem.content) as needCount, " +
			" CASE WHEN ali.userid is null THEN 0 ELSE 1 END as isOpenAliPay," +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2" +
			" from zdd_maker_studio us " +
			" join zdd_maker u on u.userid = us.userid " +
			" join zdd_maker_account ua on ua.userid = us.userid " +
			" left join zdd_system_tradecategory tc on tc.tradecategoryid = us.tradecategoryid " +
			" left join zdd_maker_bankcard ub on ub.userid = us.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" left join zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
			" left join zdd_taxer_payment tp on tp.userid = u.userid and tp.type = 1 and tp.deleted = 0 " +
			" left join zdd_platform_memorandum mem on mem.customer_id=us.userid"+
			" left join zdd_maker_alipay ali on ua.userid=ali.userid and ali.deleted=0"+
			" left join zdd_maker_bankcard ub2 on ub2.userid = us.userid and ub2.state = 1 and ub2.accttype = 2 "+
			" where if(#{taxAcctState}=0, 1=1, us.tax_acct_state = #{taxAcctState}) " +
			" AND u.maker_type = 2" +
			" and u.state != 4 " +
			" and u.expire_date < CURDATE() " +
			" and us.createtime BETWEEN #{startDate} AND #{endDate} " +
			" and (CONCAT(ua.realname,us.username) like '%${name}%') " +
			" AND IF(ISNULL(#{bindcompname}) OR #{bindcompname}='',1=1,c.compname like '%${bindcompname}%') " +
			" AND IF(ISNULL(#{tradecategoryid}) OR #{tradecategoryid}=0,1=1,us.tradecategoryid=#{tradecategoryid}) " +
			" group by us.userid " +
			" ORDER BY us.userid DESC ")
	List<StudioBriefDto> getTaxerStudioList(@Param("taxAcctState") int taxAcctState, @Param("name") String name, @Param("bindcompname") String bindcompname,
											@Param("tradecategoryid") Integer tradecategoryid, @Param("startDate") Date startDate, @Param("endDate") Date endDate);

	/**
	 * 查询承诺"创客交易未满10万"的工作室列表
	 * @return
	 */
	@Select("SELECT " +
			" zpm.* " +
			"FROM " +
			" zdd_platform_memorandum zpm " +
			" JOIN ( SELECT max( id ) AS id FROM zdd_platform_memorandum WHERE `state` = 2 GROUP BY customer_id, customer_type ) ma ON zpm.id = ma.id  " +
			"WHERE " +
			" zpm.customer_type = 1  " +
			" AND type = 4  " +
			" AND free_type IS NOT NULL  " +
			"ORDER BY " +
			" customer_id, " +
			" customer_type ")
	List<PlatformMemorandum> queryStudioByUserIds();

	/**
	 * 查询承诺"创客交易未满10万"的企业下的工作室列表
	 * @return
	 */
	@Select("SELECT " +
			" zpm.* " +
			"FROM " +
			" zdd_platform_memorandum zpm " +
			" JOIN ( SELECT max( id ) AS id FROM zdd_platform_memorandum WHERE `state` = 2 GROUP BY customer_id, customer_type ) ma ON zpm.id = ma.id  " +
			"WHERE " +
			" zpm.customer_type = 2  " +
			" AND type = 4  " +
			" AND free_type IS NOT NULL  " +
			"ORDER BY " +
			" customer_id, " +
			" customer_type ")
	List<PlatformMemorandum> queryStudioByCompany();


	@Select(" select us.*,ua.realname,ua.identity_type,ua.idno,ua.maker_label,tc.tradecategory,tp.state as paidState,ua.expert_label_state," +
			" ub.cardtype,ub.bankname,ub.acctno,ub.telephone, " +
			" ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason,ua.core_module,ua.opening_location, " +
			" ua.temporary_login,u.phone,u.usertype,u.bindcompid,c.compname as bindcompname,c.creditcode,u.compremark,u.has_company,u.has_contract,u.work_company," +
			" u.maker_type,u.payid,u.issue_date,u.expire_date," +
			" zcc.name as openingLocationName,zbm.business_manager as managerName," +
			" IF(IFNULL(us.studiotaxphoto,'')='',0,1) AS hasTaxPhoto,u.invite_code,u.notice_flag,pa.realname as inviter,count(mem.content) as needCount,  " +
			" CASE WHEN ali.userid is null THEN 0 ELSE 1 END as isOpenAliPay," +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2," +
			" ${str} "+
			" from zdd_maker_studio us " +
			" join zdd_maker u on u.userid = us.userid " +
			" join zdd_maker_account ua on ua.userid = us.userid " +
			" left join zdd_system_tradecategory tc on tc.tradecategoryid = us.tradecategoryid " +
			" left join zdd_maker_bankcard ub on ub.userid = us.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" left join zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
			" left join zdd_taxer_payment tp on tp.userid = u.userid and tp.type = 1 and tp.deleted = 0 " +
			" left join zdd_platform_memorandum mem on mem.customer_id=us.userid"+
			" left join zdd_maker_alipay ali on ua.userid=ali.userid and ali.deleted=0"+
			" left join zdd_maker_bankcard ub2 on ub2.userid = us.userid and ub2.state = 1 and ub2.accttype = 2 " +
			" left join zdd_city_config zcc on zcc.id = ua.opening_location and zcc.deleted = 0" +
			" left join zdd_company_account zca on zca.compid = c.compid " +
			" left join zdd_business_manager zbm on zbm.business_manager_id = zca.merchandiser_id"+
			" where us.state = #{state} and u.state != 4 " +
			" and us.tax_acct_state != 3 " +
			" AND IF(#{makerType} = 1, u.maker_type in (1,3), u.maker_type = #{makerType})" +
//			" and u.maker_type = #{makerType} " +
			" and us.createtime BETWEEN #{startDate} AND #{endDate} " +
			" and (ua.realname like '%${name}%' or us.username like '%${name}%') " +
			" AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa.promo_code,pa.realname) like '%${inviter}%') " +
			" AND IF(ISNULL(#{bindcompname}) OR #{bindcompname}='',1=1,c.compname like '%${bindcompname}%') " +
			" AND IF(ISNULL(#{tradecategoryid}) OR #{tradecategoryid}=0,1=1,us.tradecategoryid=#{tradecategoryid}) " +
			" AND IF( ISNULL(#{openingLocation}) , 1=1 , ua.opening_location = #{openingLocation}  )   " +
			" AND IF(ISNULL(#{temporaryLogin}), 1=1, ua.temporary_login = #{temporaryLogin})" +
			" AND IF(ISNULL(#{quotaFlag}), 1=1, us.quota_flag = #{quotaFlag})" +
			" and if(isnull(#{expertLabelState}),1=1,ua.expert_label_state=#{expertLabelState}) " +
			" and if(isnull(#{makerLabel}), 1=1, ua.maker_label like '%${makerLabel}%')" +
			" ${dockingCity} " +
			" group by us.userid having IF(#{changedDefaultRate}=0,1=1,changedDefaultRate=1) " +
			"	and IF(isnull(#{isOpenAliPay}),1=1,isOpenAliPay=#{isOpenAliPay})" +
			"	and IF(isnull(#{isOpenAccNo2}),1=1,isOpenAccNo2=#{isOpenAccNo2})" +
			" ORDER BY us.userid DESC ")
	List<StudioOpenDto> getStudioOpenList(@Param("makerType") int makerType, @Param("state") int state, @Param("name") String name, @Param(
			"inviter") String inviter, @Param("bindcompname") String bindcompname,
									   @Param("tradecategoryid") Integer tradecategoryid, @Param("startDate") Date startDate, @Param("endDate") Date endDate, @Param("openingLocation") Integer openingLocation,
									   @Param("changedDefaultRate") Integer changedDefaultRate, @Param("str") String str,
									   @Param("dockingCity") String dockingCity, @Param("temporaryLogin") Integer temporaryLogin,
									   @Param("expertLabelState")Integer expertLabelState,@Param("quotaFlag") Integer quotaFlag,
									   @Param("isOpenAliPay")Integer isOpenAliPay,@Param("makerLabel") String makerLabel,
									   @Param("isOpenAccNo2")Integer isOpenAccNo2);
	//判断是否是自然人
	@Select("select * from zdd_crowdsource_account where idno = #{idno} " +
			" ORDER BY acctid DESC " +
			" LIMIT 1 ")
	CrowdsourceAccount getNaturalPersonStatus(String idno);

	@Select("SELECT  " +
			" us.userid,  " +
			" ua.realname,  " +
			" us.username,  " +
			" us.createtime,  " +
			" us.updatetime,  " +
			" u.phone,  " +
			" us.studionickname,  " +
			" tc.tradecategory,  " +
			" c.compname AS bindcompname,  " +
			" ua.opening_location,  " +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2,  " +
			" u.platform_hosting, " +
			" sm.managername as custodian, " +
			" u.custodian_id, " +
			" ${str} " +
			"FROM  " +
			" zdd_maker_studio us  " +
			" JOIN zdd_maker u ON u.userid = us.userid  " +
			" JOIN zdd_maker_account ua ON ua.userid = us.userid  " +
			" LEFT JOIN zdd_system_tradecategory tc ON tc.tradecategoryid = us.tradecategoryid  " +
			" LEFT JOIN zdd_company c ON c.compid = u.bindcompid  " +
			" left join zdd_maker_bankcard ub2 on ub2.userid = us.userid and ub2.state = 1 and ub2.accttype = 2  " +
			" LEFT JOIN zdd_system_manager sm ON sm.managerid = u.custodian_id " +
			" WHERE u.state != 4  " +
			" AND IF(ISNULL(#{realname}) OR #{realname}='',1=1,ua.realname LIKE '%${realname}%')  " +
			" AND IF(ISNULL(#{username}) OR #{username}='',1=1,us.username LIKE '%${username}%')  " +
			" AND IF(ISNULL(#{bindcompname}) OR #{bindcompname}='',1=1,c.compname like '%${bindcompname}%')  " +
			" AND IF(ISNULL(#{platformHosting}),1=1,u.platform_hosting = #{platformHosting})  " +
			" having isOpenAccNo2 = 1 ")
	List<BankCorporateMakerDto> bankCorporateMaker(@Param("realname") String realname,
												   @Param("bindcompname") String bindcompname,
												   @Param("username") String username,
												   @Param("str") String str,
												   @Param("platformHosting") Integer platformHosting);

	@Select(" SELECT " +
			" DATE_FORMAT( open_tax_acct_finishtime, '%Y-%m-%d' ) AS years " +
			" FROM " +
			" zdd_maker_studio  " +
			" WHERE " +
			" userid = #{userid} ")
	String getDate(@Param("userid") String userid);

	@Select(" SELECT " +
			" DATE_FORMAT( open_tax_acct_finishtime, '%Y' ) AS years " +
			" FROM " +
			" zdd_maker_studio  " +
			" WHERE " +
			" userid = #{userid} ")
	String getYears(@Param("userid") String userid);

	@Select(" select count(*) from zdd_maker_studio ms " +
			" left join zdd_maker m on ms.userid = m.userid " +
			" where ms.tax_acct_state != 3 " +
			"and m.state != 4" +
			" and m.bindcompid = #{compid} ")
	int getCompUnOpenTaxAcctNum(int compid);

	@Select(" select m.userid,ms.state,m.avatar,ma.realname,ms.username,ma.realname_auth,ms.tax_acct_state" +
			" from zdd_maker m " +
			" left join zdd_maker_account ma on m.userid = ma.userid" +
			" left join zdd_maker_studio ms on m.userid = ms.userid  " +
			" where (ms.tax_acct_state is null or ms.tax_acct_state != 3) " +
			" and m.state != 4 " +
			" and m.bindcompid = #{compid} " +
			" order by m.userid ")
	List<MakerStudioUnTaxDto> findUnOpenTaxMakers(int compid);



	@Select(" select stu.userid,stu.username,stu.open_finishtime,com.compid,com.compname " +
			" from zdd_maker_studio stu   " +
			" join zdd_maker mak on mak.userid=stu.userid   " +
			" left join zdd_company com on com.compid = mak.bindcompid    " +
			" where mak.state !=4 and stu.state = 2  ")
	List<MakerStudioPromiseDto> getMaker();

	@Select("SELECT " +
			" *  " +
			" FROM " +
			" zdd_maker_studio  " +
			" WHERE " +
			" userid = #{userId} ")
	MakerStudio getMakerStudio(@Param("userId") Integer userId);

	@Select(" select ms.* from zdd_maker_studio ms " +
			" left join zdd_maker m on ms.userid = m.userid " +
			" where ms.tax_acct_state = 3 " +
			" and m.state != 4" +
			" and m.bindcompid in (#{compids}) " +
			" limit 500")
	List<MakerStudio> getCompMakerStudio(Integer compid);

}
